package com.coderman.generateSqlBatchTools.utils;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

/**
 * Created by ${fanchunshuai} on 2017-4-7.
 *
 * @version: V1.0
 * @Desc: JDBC简单工具类
 * @Copyright (c) 2017 58到家-版权所有
 */
public class JdbcUtils {

    private static Properties properties = new Properties();
    private static Connection connection = null;

    /**
     * 初始化db连接
     * @param dbConfigPath
     * @return
     * @throws Exception
     */
    public static Connection initDBConnection(String dbConfigPath)throws Exception{
        FileInputStream inStream = new FileInputStream(dbConfigPath);

        properties.load(inStream);

        // 1. 准备获取连接的 4 个字符串: user, password, url, jdbcDriver
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url= properties.getProperty("url");
        String jdbcDriver= properties.getProperty("jdbcDriver");

        // 2. 加载驱动: Class.forName(driverClass)

        Class.forName(jdbcDriver);
        // 3.获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        return  connection;
    }


    /**
     * 执行单条语句
     * @param sql
     * @return
     * @throws Exception
     */
    public static boolean exeOneSql(String sql)throws Exception{
        if(connection == null){
            System.out.println("connection is null ,please init it....");
            return false;
        }
        //保存当前自动提交模式
        boolean autoCommit = connection.getAutoCommit();
        //关闭自动提交
        connection.setAutoCommit(false);
        connection.setAutoCommit(false);
        try {
            connection.prepareStatement(sql).execute();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;

        }catch (Exception e){
            connection.rollback();
            return false;
        }

    }

    /**
     * 批量执行多条语句
     * @param sqlList
     * @return
     * @throws Exception
     */
    public static boolean exeBatchSql(List<String> sqlList) throws Exception{
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return false;
            }
            //保存当前自动提交模式
            boolean autoCommit=connection.getAutoCommit();
            //关闭自动提交
            connection.setAutoCommit(false);
            connection.setAutoCommit(false);
            Statement preparedStatement = connection.createStatement();

            for (String sql : sqlList){
                preparedStatement.addBatch(sql);
            }
            preparedStatement.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
            return false;
        }
    }

    public static void main(String[] args) throws Exception {
        String sqlArray [] = new String[4];
        sqlArray[0] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[1] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[2] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[3] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";

        String sql = "delete from urls where id in (318657,318658,318659,318660,318661)";
        JdbcUtils.initDBConnection("E:\\djworkspace\\openResource\\generateSqlBatchFromExcel\\generatesqlbatchfromexcel\\sqlBatchTools\\src\\main\\resources\\jdbc.properties");

        boolean b = JdbcUtils.exeOneSql(sql);
        System.out.println("b = "+b);

        //boolean b = JdbcUtils.exeBatchSql(sqlArray);
        //System.out.println("b = "+b);
    }

}
